﻿Imports System.Data.SqlClient
Module ModuleTong
    Public sLuong As String
    Public DTich As String
End Module
Public Class FrmTonghopTheoHuyen
    Private Sub FrmTonghop_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DateTimeTuNgay.Text = Date.Today()
        DateTimeDenNgay.Text = Date.Today()
        DataGridViewBD.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBD.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub
    Private Sub Sum()
        ob_cnn.Open()
        Dim tbl As DataTable
        Dim dbComm As SqlCommand
        Dim TuNgay As String
        TuNgay = String.Format("{0:MM/dd/yyyy}", DateTimeTuNgay.Value)
        Dim DenNgay As String
        DenNgay = String.Format("{0:MM/dd/yyyy}", DateTimeDenNgay.Value)
        dbComm = New SqlCommand("select Sum(soluongbd) as soluong, sum(dientich1) as dientich " &
                               " From  BIENDONG  WHERE " &
                               "  datediff (day, ngaynhap, '" & TuNgay & "') <= 0  " &
                               " and datediff (day, ngayNhap, '" & DenNgay & "') >= 0 ", ob_cnn)
        tbl = New DataTable()
        tbl.Load(dbComm.ExecuteReader())
        Dim dr As DataRow
        dr = tbl.Rows(0)
        sLuong = dr("soluong").ToString()
        DTich = dr("dientich").ToString()
        TextBoxSLuong.Text = sLuong
        TextBoxDienTich.Text = DTich
        ob_cnn.Close()
    End Sub
    Private Sub TinhTong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        Dim TuNgay As String
        TuNgay = String.Format("{0:MM/dd/yyyy}", DateTimeTuNgay.Value)
        Dim DenNgay As String
        DenNgay = String.Format("{0:MM/dd/yyyy}", DateTimeDenNgay.Value)
        dbComm = New SqlCommand("select h.T_huyen, min(ngaynhap) as tungay, max(ngaynhap) as denngay, Sum(soluongbd) as soluong, sum(dientich1) as dientich " &
                               " From  BIENDONG bd, TENHUYEN h  WHERE " &
                               "  datediff (day, ngaynhap, '" & TuNgay & "') <= 0  " &
                               " and datediff (day, ngayNhap, '" & DenNgay & "') >= 0 " &
                               " and bd.DiachiHuyenID= h.maH group by h.T_huyen", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBD.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub


    Private Sub Buttimkiem_Click(sender As Object, e As EventArgs) Handles Buttimkiem.Click
        TinhTong()
        sum()
    End Sub
    Private Sub DataGridViewBD_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBD.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBD.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBD.RowHeadersWidth < Size.Width + 20 Then DataGridViewBD.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub ButtonXuatExcel_Click(sender As Object, e As EventArgs) Handles ButtonXuatExcel.Click

    End Sub
End Class